import pymysql
from itemadapter import ItemAdapter
from beautifultable import BeautifulTable
import logging
class BlanksPipeline:
    def __init__(self):
        self.db = None
        self.cursor = None

    def open_spider(self, spider):
        try:
            self.db = pymysql.connect(
                host='localhost',
                user='root',
                password='826922',
                database='stocks_db',
                charset='utf8mb4',
                port=3306
            )
            self.cursor = self.db.cursor()
            spider.logger.info("MySQL数据库连接成功")

            # 连接成功后才创建表
            self.create_table(spider)

        except Exception as e:
            spider.logger.error(f"MySQL连接失败: {e}")
            self.db = None
            self.cursor = None

    def create_table(self, spider):
        try:
            self.cursor.execute("""
                CREATE TABLE IF NOT EXISTS exchange_rates (
                    id INTEGER PRIMARY KEY AUTO_INCREMENT,
                    current VARCHAR(50) NOT NULL,
                    spot_buy DECIMAL(10,2),
                    cash_buy DECIMAL(10,2),
                    spot_sell DECIMAL(10,2),
                    cash_sell  DECIMAL(10,2),
                    bank_rate DECIMAL(10,2),
                    publish_date DATE NOT NULL,
                    publish_time TIME,
                    crawl_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    UNIQUE(current, publish_date)
                )
            """)
            spider.logger.info("数据表 stocks 创建成功或已存在")
        except Exception as e:
            spider.logger.error(f"创建表失败: {e}")

    def process_item(self, item, spider):
        if not self.db or not self.cursor:
            spider.logger.error("数据库未连接，跳过数据插入")
            return item

        adapter = ItemAdapter(item)
        try:
            # 2. 插入数据到数据库
            self.cursor.execute('''
                REPLACE INTO exchange_rates (
                    current, spot_buy, cash_buy, spot_sell, cash_sell, 
                    bank_rate, publish_date, publish_time
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
            ''', (
                adapter.get('current'),
                adapter.get('spot_buy') or None,  # 空值存为 NULL
                adapter.get('cash_buy') or None,
                adapter.get('spot_sell') or None,
                adapter.get('cash_sell') or None,
                adapter.get('bank_rate') or None,
                adapter.get('publish_date'),
                adapter.get('publish_time') or None
            ))
            self.db.commit()
            spider.logger.debug(f"数据插入成功: {adapter.get('current')}")
        except Exception as e:
            self.db.rollback()
            spider.logger.error(f"数据插入失败: {e}")
        return item

    def close_spider(self, spider):
        # 关闭数据库连接
        if self.db:
            self.db.close()
            spider.logger.info("数据库连接已关闭")